import pymysql as mdb
from setting import DB, CSV_PATH
import xlwt


class DataSave(object):
    db_conn = mdb.connect(
        host=DB.get('host'),
        port=DB.get('port'),
        user=DB.get('user'),
        passwd=DB.get('passwd'),
        db=DB.get('db')
    )

    # 插入 数据库 表 day_data
    @staticmethod
    def insert_db_day_data(self, result):
        conn = self.db_conn
        cursor = conn.cursor()
        cursor.executemany(
            'INSERT INTO day_data(cityName,time_point,aqi,pm2_5,pm10,so2,no2,co,o3,rank,quality) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
            result)
        conn.commit()
        conn.close()

    # 插入 数据库 表 month_data
    @staticmethod
    def insert_db_month_data(self, result):
        conn = self.db_conn
        cursor = conn.cursor()
        cursor.executemany(
            'INSERT INTO month_data(cityName,time_point,aqi,max_aqi,min_aqi,pm2_5,pm10,so2,no2,co,o3,rank,quality) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
            result)
        conn.commit()
        conn.close()

    # 输出到控制台
    @staticmethod
    def console_print(self, result):
        for res in result:
            print(res)

    # 保存为 csv
    @staticmethod
    def write_csv(self, result):
        import csv
        filename = CSV_PATH
        out = open(filename, 'a', newline='')
        csv_write = csv.writer(out, dialect='excel')
        for res in result:
            csv_write.writerow(res)

    # 保存为 excel 文件
    @staticmethod
    def write_excel(self, result, city_name, data_type):
        wbk = xlwt.Workbook()
        sheet = wbk.add_sheet(city_name)
        for k, v in enumerate(data_type):
            sheet.write(0, k, v)
        for k, v in enumerate(result):
            for k1, v1 in enumerate(data_type):
                sheet.write(k + 1, k1, v[v1])
        wbk.save('./day/' + city_name + '.xls')